﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace KetNoi
{
    public class clsKetNoi
    {
        SqlConnection con = new SqlConnection();

        //string KetNoiSQL = @"Data Source=CHI-PC\SQLEXPRESS;Initial Catalog=QuanLyTour;User ID=sa; password=123456; Max Pool Size=75;Min Pool Size=5";
        string KetNoiSQL = @"Data Source=CHI-PC\SQLEXPRESS;Initial Catalog=QuanLyTour;Integrated Security=True";
        public clsKetNoi()
        {
            //con.ConnectionString = KetNoiSQL;
            //if (con.State == System.Data.ConnectionState.Closed)
            //    con.Open();
            con = new SqlConnection(KetNoiSQL);
            con.Open();
        }

        public DataTable LayDuLieu(string sql)
        {
            try
            {
                SqlCommand command = new SqlCommand(sql, con);
                command.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

        public DataTable TimKiem(string sql, string[] name, object[] value, int Nparameter)
        {
            try
            {
                SqlCommand command = new SqlCommand(sql, con);
                command.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < Nparameter; i++)
                {
                    command.Parameters.AddWithValue(name[i], value[i]);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

        public int CapNhat(string sql)
        {
            try
            {
                SqlCommand command = new SqlCommand(sql, con);
                command.CommandType = CommandType.StoredProcedure;
                return command.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

        public int CapNhat(string sql, string[] name, object[] value, int Nparameter)
        {
            try
            {
                SqlCommand command = new SqlCommand(sql, con);
                command.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < Nparameter; i++)
                {
                    command.Parameters.AddWithValue(name[i], value[i]);
                }
                return command.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

    }
}
